<?php

class CanBoDGVaDBL extends BaseController {

	/**
	 * Display a listing of the resource.
	 *
	 * @return Response
	 */
	public function index()
	{
        return View::make('canbo.dg')->with('title','Đánh giá sinh viên');
	}
	public function index1()
	{
        return View::make('canbo.duyetbl')->with('title','Duyệt bảo lưu');
	}
   
        
	/**
	 * Show the form for creating a new resource.
	 *
	 * @return Response
	 */
	public function create()
	{
        return View::make('canbodgvadbls.create');
	}

	/**
	 * Store a newly created resource in storage.
	 *
	 * @return Response
	 */
	 public function getNhomSvDK($idcb){
        $nhomdk=DB::table('chi_tiet_can_bo_huong_dan')
            ->join('de_tai_luan_van',function($join)
        	{
                        $join->on('de_tai_luan_van.id','=','chi_tiet_can_bo_huong_dan.id_de_tai');
                }
                 )
            ->join('nhom_dang_ki',function($join) 
        	{
                        $join->on('nhom_dang_ki.id_de_tai','=','chi_tiet_can_bo_huong_dan.id_de_tai');
                }
                 )
            ->select('nhom_dang_ki.ms_nhom','de_tai_luan_van.ten_de_tai','de_tai_luan_van.id')
            ->where('chi_tiet_can_bo_huong_dan.id_can_bo','=',$idcb)
            ->where('nhom_dang_ki.trang_thai_th','=',1)
            ->where('de_tai_luan_van.id_hoc_ki_nien_khoa','=',DeTaiLuanVan::max('id_hoc_ki_nien_khoa'))
            ->groupBy('nhom_dang_ki.ms_nhom')
            ->get();
        return($nhomdk);
    }
//  lấy nhận xét của giảng viên về sinh viên thực hiện đề tài
    public function getNhanXet($ms_nhom){
    $resource = DB::table('nhom_dang_ki')
             ->join('danh_gia_sv',function($join) 
            {
                        $join->on('danh_gia_sv.id_sinh_vien','=','nhom_dang_ki.id_sinh_vien');
                }
                 )
            ->select('danh_gia_sv.danh_gia')
            ->where('nhom_dang_ki.ms_nhom',$ms_nhom)
            ->distinct()
            ->first();
        return $resource;
    }

//    
    public function getTuan($idkh){
        $tuan = DB::table('chi_tiet_ke_hoach')
                ->select('tuan')
                ->where('id_ke_hoach',$idkh)
                ->distinct()
                ->get();
        return $tuan;
    }
// Ham truyen ten de tai, ma de tai sang trang ghidanhgia
    public function getThongTinDkdt($iddt){
        $resource=KeHoachHuongDan::select('id')
             ->where('id_de_tai',$iddt)
             ->get();
        foreach ($resource as $key ) {
            $id_ke_hoach=$key->id;
        }
        $thong_tin=DB::table('nhom_dang_ki')
                ->join('de_tai_luan_van',function($join)
        	{
                        $join->on('nhom_dang_ki.id_de_tai','=','de_tai_luan_van.id');
                }
                )
                ->join('sinh_vien',function($join) 
        	{
                        $join->on('nhom_dang_ki.id_sinh_vien','=','sinh_vien.id');
                }
                )
                    ->select('de_tai_luan_van.ten_de_tai','nhom_dang_ki.ms_nhom')
                    ->where('nhom_dang_ki.id_de_tai','=',$iddt)
                    ->get();
               return View::make('canbo.ghidanhgia')
                ->with('title','Ghi Đánh Giá')
                ->with('thong_tin',$thong_tin)
                ->with('iddt',$iddt)
                ->with('id_ke_hoach',$id_ke_hoach);
    }
// Ham tra ve  Ho ten sinh vien dang ki de tai dua vao ma so nhom
    public function getThongTindt($msnhom){
        $thong_tin=DB::table('nhom_dang_ki')
            ->join('sinh_vien',function($join) 
        	{
                        $join->on('nhom_dang_ki.id_sinh_vien','=','sinh_vien.id');
                }
                )
                ->select('sinh_vien.ho','sinh_vien.ten')
                ->where('nhom_dang_ki.ms_nhom','=',$msnhom)
                ->get();
                return ($thong_tin);
    }
//Ham tr ve cong viec cua sinh vien thuc hien de tai dua vao id de tai va tuan.
    public function getMoTaCongViec($iddt,$tuan)
    {
        $id_ctkh=DB::table('chi_tiet_ke_hoach')
                
                                ->Join('ke_hoach_huong_dan',function($join) 
                                    {
                                            $join->on('chi_tiet_ke_hoach.id_ke_hoach','=','ke_hoach_huong_dan.id');
                                    }              
                                    )
                                ->Join('nhom_dang_ki',function($join) 
                                    {
                                            $join->on('ke_hoach_huong_dan.id_de_tai','=','nhom_dang_ki.id_de_tai');
                                    }
                                    )
                                ->select('chi_tiet_ke_hoach.id')
                                ->where('nhom_dang_ki.id_de_tai','=',$iddt)
                                ->where('chi_tiet_ke_hoach.tuan','=',$tuan)
                                ->get();

        foreach ($id_cv as $key) 
        {
            $a=$key->id_cv;
        }  
       foreach ($id_ctkh as $key) 
       {
            $id=$key->id;

        }
        $cong_viec=DB::table('chi_tiet_ke_hoach')
        ->Join('cong_viec',function($join) 
                            {
                                    $join->on('chi_tiet_ke_hoach.id_cv','=','cong_viec.id');
                            }              
                            )
        ->Join('muc_do_hoan_thanh',function($join) 
                            {
                                    $join->on('chi_tiet_ke_hoach.id_dg','=','muc_do_hoan_thanh.id');
                            }              
                            )
                    ->select('cong_viec.mo_ta_cong_viec','muc_do_hoan_thanh.mo_ta_muc_do')
                    ->where('chi_tiet_ke_hoach.id',$id)
                    ->get();
         return $cong_viec;
         
    }
//Ham tra ve mo ta danh gia trong table MucDoHoanThanh
public function getDanhGia()
{
        $danhgia=MucDoHoanThanh::select('id','mo_ta_muc_do')
                    ->where('hien_thi','=',1)
                    ->distinct()
                    ->get();
        return $danhgia;
}
//Ham insert noidung danhgia_cuoiki cho sinh vien
public function InsDgia($nddanhgia,$id){
    $trangthai=DB::table('danh_gia_sv')
            ->where('id_sinh_vien',$id)
            ->update(
                array('danh_gia' => $nddanhgia));
}
// Ham luu ket qua danh gia cuoi ki cua nhom theo de tai :
    public function DanhGiaCuoiki(){
        $nddanhgia = Input::get('txtDanhGia');
        $msnhom = Input::get('txtmsnhom');
        $id_sv=DB::table('nhom_dang_ki')
            ->select('id_sinh_vien')
            ->where('ms_nhom','=',$msnhom)
            ->get();
        foreach ($id_sv as $key ) {
           // InsDgia($nddanhgia,$key->id_sinh_vien);
            $id=$key->id_sinh_vien;
            $this->InsDgia($nddanhgia,$id);
        }
        return Redirect::to('/cb/4/dg')->with('trangthai',$this);
    }
	/**
	 * Display the specified resource.
	 *
	 * @param  int  $id
	 * @return Response
	 */
    public function lay_danh_gia_sinh_vien($mssv)
    {
        $array = DB::table('danh_gia_sv')
        ->join('sinh_vien', function($join)
            {
                $join->on('danh_gia_sv.id_sinh_vien','=','sinh_vien.id');
            })
        ->select('danh_gia_sv.diem_chu')  
        ->where('sinh_vien.mssv','=',$mssv)
        ->first();
        return $array;
    }
    public function lay_trang_thai_nhom($msnhom)
    {
        $array = DB::table('nhom_dang_ki')
        	->select('*')  
        	->where('nhom_dang_ki.ms_nhom','=',$msnhom)
        	->get();
        return $array;
    }
    public function lay_danh_sach_nhom_bao_luu($idcanbo)
    {
    	$HomeController = new HomeController();
    	$idhknkht = $HomeController->getIdHknkMax();
    	$array = DB::table('de_tai_luan_van')
        ->join('chi_tiet_can_bo_huong_dan', function($join)
            {
                $join->on('chi_tiet_can_bo_huong_dan.id_de_tai','=','de_tai_luan_van.id');
            })
        ->join('nhom_dang_ki', function($join)
            {
                $join->on('nhom_dang_ki.id_de_tai','=','de_tai_luan_van.id');
            })    
		->select('nhom_dang_ki.ms_nhom','nhom_dang_ki.trang_thai_th','de_tai_luan_van.id','de_tai_luan_van.ten_de_tai')  
        ->where('chi_tiet_can_bo_huong_dan.id_can_bo','=',$idcanbo,'and')
        ->where('nhom_dang_ki.trang_thai_th','=','0','and')
        ->where('de_tai_luan_van.id_hoc_ki_nien_khoa','=',$idhknkht)
        ->distinct()
        ->get();
        return $array;
    }
    public function lay_id_chi_tiet_huong_dan($iddetai, $idcanbo)
    {
        $array = DB::table('chi_tiet_can_bo_huong_dan')
        ->select('chi_tiet_can_bo_huong_dan.id')  
        ->where('chi_tiet_can_bo_huong_dan.id_de_tai','=',$iddetai)
        ->where('chi_tiet_can_bo_huong_dan.id_can_bo','=',$idcanbo)
        ->first();
        return $array;
    }
    public function duyet_bao_luu()
    {
    	$ngaygio = date("Y-m-d h:i:s", time());
        $masonhom = Input::get('txtMSNhom');
        $ketqua = DB::table('nhom_dang_ki')
            ->where('ms_nhom', '=', $masonhom)
            ->update(array('trang_thai_th' => 2,
            				'tg_duyet' => $ngaygio));
        return Redirect::back()->with('ketqua', $ketqua);
    }
    public function show($id)
	{
        return View::make('canbodgvadbls.show');
	}

	/**
	 * Show the form for editing the specified resource.
	 *
	 * @param  int  $id
	 * @return Response
	 */
	public function edit($id)
	{
        return View::make('canbodgvadbls.edit');
	}

	/**
	 * Update the specified resource in storage.
	 *
	 * @param  int  $id,$ms_nhom
	 * @return Response
	 */
	public function UpdateDanhGiaTuan()
	{
            $txtmsnhom=Input::Get('txtmsn');
            $id_dg=Input::Get('danhgia');
            $tuan=Input::Get('txttuan');
            $Resource =DB::table('nhom_dang_ki')
                    ->Join('ke_hoach_huong_dan',function($join) 
                    {
                            $join->on('ke_hoach_huong_dan.id_de_tai','=','nhom_dang_ki.id_de_tai');
                    }              
                    )
                   ->select('ke_hoach_huong_dan.id')
                   ->where('nhom_dang_ki.ms_nhom','=',$txtmsnhom)
                   ->get();
            foreach ($Resource as $key ) {
               $a=$key->id;
            }
            $trangthai=DB::table('chi_tiet_ke_hoach')
                        ->where('id_ke_hoach',$a)
                        ->where('tuan',$tuan)
                        ->update(array('id_dg' => $id_dg));
               // UPDATE chi_tiet_ke_hoach SET id_dg=10 WHERE id_ke_hoach = 64 and tuan='1,2'
            return Redirect::back()->with('trangthai',$trangthai);
	}
	public function lay_ds_sinh_vien($msnhom) 
	{
		$array = DB::table('nhom_dang_ki')
			->select('*')
			->where('ms_nhom','=',$msnhom)
			->get();
		return $array;	
	}
	public function lay_ds_sinh_vien_xin_bao_luu($msnhom) 
	{
		$array = DB::table('nhom_dang_ki')
			->select('*')
			->where('ms_nhom','=',$msnhom,'and')
			->where('trang_thai_th','=','0')
			->get();
		return $array;	
	} 
	/**
	 * Remove the specified resource from storage.
	 *
	 * @param  int  $id
	 * @return Response
	 */
	public function destroy($id)
	{
		//
	}

}
